# 03_funnel_analysis.py - 用户转化漏斗分析
import pandas as pd
import sqlite3


def funnel_analysis(csv_file_path):
    """
    用户转化漏斗分析
    输入: CSV文件路径
    输出: 漏斗分析结果的CSV文件
    """
    print("=== 开始用户转化漏斗分析 ===")

    # 读取数据
    df = pd.read_csv(csv_file_path)
    conn = sqlite3.connect(':memory:')
    df.to_sql('user_personalized_features', conn, index=False, if_exists='replace')

    # 详细的漏斗数据
    funnel_sql = """
    WITH FunnelData AS (
        SELECT 
            User_ID,
            Age,
            Gender,
            Location,
            Interests,
            -- 获取阶段：所有用户
            1 as 获取用户,

            -- 激活阶段：有显著网站交互行为的用户
            CASE WHEN Time_Spent_on_Site_Minutes > 10 OR Pages_Viewed > 5 THEN 1 ELSE 0 END as 激活用户,

            -- 留存阶段：近期活跃用户
            CASE WHEN Last_Login_Days_Ago <= 30 THEN 1 ELSE 0 END as 留存用户,

            -- 收入阶段：有消费用户
            CASE WHEN Total_Spending > 0 THEN 1 ELSE 0 END as 付费用户,

            -- 高价值用户阶段
            CASE WHEN Total_Spending > (SELECT AVG(Total_Spending) FROM user_personalized_features) 
                 AND Last_Login_Days_Ago <= 30 
                 AND Purchase_Frequency > (SELECT AVG(Purchase_Frequency) FROM user_personalized_features) 
                 THEN 1 ELSE 0 END as 高价值用户,

            -- 用户行为指标
            Time_Spent_on_Site_Minutes,
            Pages_Viewed,
            Total_Spending,
            Purchase_Frequency,
            Last_Login_Days_Ago,
            Product_Category_Preference,
            Newsletter_Subscription
        FROM user_personalized_features
    )
    SELECT * FROM FunnelData;
    """

    funnel_detailed = pd.read_sql_query(funnel_sql, conn)

    # 生成漏斗汇总数据
    funnel_summary = pd.DataFrame({
        '阶段': ['获取用户', '激活用户', '留存用户', '付费用户', '高价值用户'],
        '用户数量': [
            funnel_detailed['获取用户'].sum(),
            funnel_detailed['激活用户'].sum(),
            funnel_detailed['留存用户'].sum(),
            funnel_detailed['付费用户'].sum(),
            funnel_detailed['高价值用户'].sum()
        ],
        '描述': [
            '总用户数量',
            '有显著交互行为的用户',
            '30天内活跃的用户',
            '有过消费行为的用户',
            '高消费、高频率的活跃用户'
        ]
    })

    # 计算转化率
    funnel_summary['绝对转化率'] = round(funnel_summary['用户数量'] / funnel_summary.loc[0, '用户数量'] * 100, 2)

    funnel_summary['阶段转化率'] = 100.0
    for i in range(1, len(funnel_summary)):
        prev_count = funnel_summary.loc[i - 1, '用户数量']
        current_count = funnel_summary.loc[i, '用户数量']
        if prev_count > 0:
            funnel_summary.loc[i, '阶段转化率'] = round((current_count / prev_count) * 100, 2)

    # 分群漏斗分析
    print("生成分群漏斗数据...")

    # 按地理位置分群
    location_funnel_sql = """
    SELECT 
        Location,
        COUNT(*) as 获取用户,
        SUM(CASE WHEN Time_Spent_on_Site_Minutes > 10 OR Pages_Viewed > 5 THEN 1 ELSE 0 END) as 激活用户,
        SUM(CASE WHEN Last_Login_Days_Ago <= 30 THEN 1 ELSE 0 END) as 留存用户,
        SUM(CASE WHEN Total_Spending > 0 THEN 1 ELSE 0 END) as 付费用户
    FROM user_personalized_features
    GROUP BY Location
    """
    location_funnel = pd.read_sql_query(location_funnel_sql, conn)

    # 按兴趣分群
    interest_funnel_sql = """
    SELECT 
        Interests,
        COUNT(*) as 获取用户,
        SUM(CASE WHEN Time_Spent_on_Site_Minutes > 10 OR Pages_Viewed > 5 THEN 1 ELSE 0 END) as 激活用户,
        SUM(CASE WHEN Last_Login_Days_Ago <= 30 THEN 1 ELSE 0 END) as 留存用户,
        SUM(CASE WHEN Total_Spending > 0 THEN 1 ELSE 0 END) as 付费用户
    FROM user_personalized_features
    GROUP BY Interests
    """
    interest_funnel = pd.read_sql_query(interest_funnel_sql, conn)

    # 保存结果
    funnel_detailed.to_csv('funnel_detailed_data.csv', index=False, encoding='utf-8-sig')
    funnel_summary.to_csv('funnel_summary.csv', index=False, encoding='utf-8-sig')
    location_funnel.to_csv('funnel_by_location.csv', index=False, encoding='utf-8-sig')
    interest_funnel.to_csv('funnel_by_interest.csv', index=False, encoding='utf-8-sig')

    print("漏斗分析完成！")
    print(f"总体转化漏斗:\n{funnel_summary}")
    print(f"\n结果文件已保存:")
    print("- funnel_detailed_data.csv (详细漏斗数据)")
    print("- funnel_summary.csv (漏斗汇总)")
    print("- funnel_by_location.csv (按地理位置分群)")
    print("- funnel_by_interest.csv (按兴趣分群)")

    conn.close()

    return {
        'summary': funnel_summary,
        'detailed': funnel_detailed,
        'by_location': location_funnel,
        'by_interest': interest_funnel
    }


if __name__ == "__main__":
    # 使用示例
    funnel_analysis('user_personalized_features.csv')